package br.com.stefanini.provajava.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import br.com.stefanini.provajava.model.Experiencia;
import br.com.stefanini.provajava.model.util.DataBaseUtil;

public class ExperienciaDAO {

	private Connection connection;

	public ExperienciaDAO() {
		connection = DataBaseUtil.getConnection();
	}

	public void addExperiencia(Integer idProfissional, Experiencia experiencia) {
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement("INSERT INTO TB_EXPERIENCIA (ID_EXPERIENCIA, ID_PROFISSIONAL, ST_EMPRESA, ST_ATIVIDADES) VALUES (SQ_EXPERIENCIA.NEXTVAL, ?, ?, ? )");
			preparedStatement.setInt(1, idProfissional);
			preparedStatement.setString(2, experiencia.getEmpresa());
			preparedStatement.setString(3, experiencia.getAtividades());
			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void deleteExperiencia(Integer idExperiencia) {
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement("DELETE FROM TB_EXPERIENCIA WHERE ID_EXPERIENCIA = ?");
			preparedStatement.setInt(1, idExperiencia);
			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public void updateProfissional(Experiencia experiencia) {
		try {
			PreparedStatement preparedStatement = connection
					.prepareStatement("UPDATE TB_EXPERIENCIA SET ST_EMPRESA = ?, ST_ATIVIDADES = ? WHERE ID_EXPERIENCIA = ?");
			preparedStatement.setString(1, experiencia.getEmpresa());
			preparedStatement.setString(2, experiencia.getAtividades());
			preparedStatement.setInt(3, experiencia.getId());
			preparedStatement.executeUpdate();

		} catch (SQLException e) {
			e.printStackTrace();
		}
	}

	public List<Experiencia> getAllExperienciaByProfissional(
			Integer idProfissional) {
		List<Experiencia> retorno = new ArrayList<Experiencia>();
		try {
			Statement statement = connection.createStatement();
			ResultSet rs = statement
					.executeQuery("SELECT * FROM TB_EXPERIENCIA WHERE ID_PROFISSIONAL = ?");
			while (rs.next()) {
				Experiencia experiencia = new Experiencia();
				experiencia.setId(rs.getInt("ID_EXPERIENCIA"));
				experiencia.setIdProfissional(rs.getInt("ID_PROFISSIONAL"));
				experiencia.setEmpresa(rs.getString("ST_EMPRESA"));
				experiencia.setAtividades(rs.getString("ST_ATIVIDADES"));
				retorno.add(experiencia);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return retorno;
	}
}
